SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[_PrescriptionDetail_Print]
    @PrescriptionID NVARCHAR(20)
AS 
    WITH    PrescriptionDetail
              AS ( SELECT   PrescriptionDetail.DrugId ,
                            PrescriptionDetail.DrugName ,
                            PrescriptionDetail.Unit ,
                            Quantity ,
                            UnitPrice ,
                            PrescriptionDetail.CostPrice ,
                            ExpDate ,
                            BatchLot ,
                            PrescriptionDetail.IsControlDrug
                   FROM     dbo.PrescriptionDetail
                   WHERE    PrescriptionID = @PrescriptionID
                 )
        SELECT  MAX(PrescriptionDetail.DrugId) AS DrugId ,
                MAX(PrescriptionDetail.DrugName) AS DrugName ,
                MAX(PrescriptionDetail.Unit) AS Unit ,
                SUM(PrescriptionDetail.Quantity) AS Quantity ,
                MAX(PrescriptionDetail.UnitPrice) AS UnitPrice ,
                MAX(PrescriptionDetail.CostPrice) AS CostPrice ,
                IsControlDrug,
                STUFF(( SELECT  '/ ' + BatchLot + ' - '
                                + SUBSTRING(CONVERT(VARCHAR, ExpDate, 102), 0,
                                            8)
                        FROM    PrescriptionDetail PrescriptionDetail1
                        WHERE   PrescriptionDetail.DrugId = PrescriptionDetail1.DrugId
                      FOR
                        XML PATH('')
                      ), 1, 1, '') AS Remark
        FROM    PrescriptionDetail
        GROUP BY PrescriptionDetail.DrugId,IsControlDrug
        ORDER BY PrescriptionDetail.DrugId ASC
        
GO
